const express = require('express');
const db = require('../utils/db');
const router = express.Router();
// 渲染
router.post('/getBalance', (req, res) => {
    const { search } = req.body;
    const sql = `select * from balance where username like '%${search}%' `;
    db.query(sql, (err, data) => {
        if (err) {
            res.send({ error: 1, msg: err })
        } else {
            if (data.length > 0) {
                res.send({ error: 0, data: data })
            } else {
                res.send({ error: 1, msg: err })
            }

        }
    })
})
// 高级搜索
router.post('/getSenior', (req, res) => {
    const { username, type, status } = req.body;
    const sql = `select * from balance where username like '%${username}%' and type ='${type}' and balance.status=${status} `;
    db.query(sql, (err, data) => {
        if (err) throw err;
        res.send({ data: data, code: 200 })
    })
})
// 删除
router.delete('/delBalance', (req, res) => {
    const { id } = req.body;
    const sql = `delete from balance where balance.key = ${id}`;
    db.query(sql, (err, data) => {
        if (err) throw err;
        res.send({ code: 200, msg: '成功' })
    })
})

// 添加
// INSERT INTO `balance` (`username`, `balance`, `createdate`, `type`, `status`, `balmsg`) VALUES ('ccc', '10', '2022-12-11 15:32:42', '缴费', '2', '测试')
router.post('/addBalance', (req, res) => {
    const { username, balance, type, status, balmsg } = req.body;
    const oDate = new Date();
    let day = oDate.toLocaleString();
    const sql = `insert into balance values ("${username}","${balance}","${day}","${type}",null,"${status}","${balmsg}")`;
    db.query(sql, (err, data) => {
        if (err) throw err;
        res.send({ code: 200, msg: '成功' })
    })
});

// 修改
// UPDATE `balance` SET `balance`='111', `status`='1' WHERE (`key`='12')
router.post('/editBalance', (req, res) => {
    console.log(req.body);
    const { username, balance, status, key } = req.body;
    const sql = `UPDATE balance SET 
    username='${username}', balance='${balance}', status='${status}' 
    WHERE balance.key=${key}; `
    console.log(sql);
    db.query(sql, (err, data) => {
        if (err) throw err;
        res.send({ code: 200, msg: '成功' })
    })
})
module.exports = router;